package com.hd.appcommon.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.hd.appcommon.entity.StudentCourse;
import com.hd.appcommon.entity.vo.CourseTableExcelVo;
import com.hd.appcommon.entity.vo.CourseTableVo;
import com.hd.hdxgddcommon.page.MyPage;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface StudentCourseMapper extends BaseMapper<StudentCourse> {
    @Select("SELECT 1 from student_course where 'student_job_number'=#{studentJobNumber} " +
            "and 'course_specific_code'=#{courseSpecificCode} " +
            "and 'school_year' =#{schoolYear} and 'term'=#{term} limit 1")
    Integer isExist(@Param("studentJobNumber") String studentJobNumber,
                    @Param("courseSpecificCode") String courseSpecificCode,
                    @Param("schoolYear") String schoolYear,
                    @Param("term") String term);



    /*五表联查，需要固定必要条件！*/
    /*专业表较小可以不加入查询条件，
    * 学生表与专业表联查受专业表影响应加入查询条件*/
    @Select("select name,job_number,college_name, major_name,course_specific_code,course_name,course_type,school_year_term, cour_credit from\n" +
            "(\n" +
            "select user.job_number,user.ddid from user \n" +
            "where  \n" +
            "\t\t(case when user.name like #{studentName}  then (case when  #{studentName}  is not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{studentName}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            "\t\t and\n" +
            "\t\t (case when user.job_number=#{jobNumber}  then (case when  #{jobNumber}  is not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{jobNumber}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            ") as A left join \n" +
            "(\n" +
            "select concat(student_course.school_year,'-',student_course.term) as school_year_term,student_course.student_job_number,student_course.course_specific_code\n" +
            " from student_course \n" +
            "where  \n" +
            "\t\t(case when student_course.school_year=#{schoolYear}  then (case when  #{schoolYear}  is not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{schoolYear}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            "\t\t and\n" +
            "\t\t (case when student_course.term=#{term}  then (case when  #{term}  is not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{term}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            ")as B on A.job_number=B.student_job_number left join \n" +
            "(\n" +
            "\n" +
            "select course.cour_credit, course.cour_name as course_name, cour_attr as course_type,course.cour_code_specific from course\n" +
            "where  \n" +
            "\t\t(case when course.cour_name like #{courseName}  then (case when  #{courseName}  is not null then '1' else '0' end)\n" +
            "\t\t\t\n" +
            "\t\t\t else (case when  #{courseName}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            "\t\t and\n" +
            "\t\t (case when course.cour_attr = #{courseType}  then (case when #{courseType} is not null then '1' else '0' end)\n" +
            "\t\t\t\n" +
            "\t\t\t else (case when #{courseType} is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            ") as C on B.course_specific_code=C.cour_code_specific \n" +
            "left join student on A.ddid=student.user_id left join \n" +
            "(select major.name as major_name,major.college_name, major.`code` from major where  \n" +
            "case when major.college_name like #{collegeName}  then (case when  #{collegeName}\t\tis not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{collegeName}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') as D on D.code=student.major_id")
    IPage<CourseTableVo> getCourseTableVo(MyPage page,
                                          @Param("collegeName") String collegeName,
                                          @Param("studentName") String studentName,
                                          @Param("jobNumber") String jobNumber,
                                          @Param("schoolYear") String schoolYear,
                                          @Param("term") String term,
                                          @Param("courseName") String courseName,
                                          @Param("courseType") String courseType);

    @Select("select name,job_number,college_name, major_name,course_specific_code,course_name,course_type,school_year_term, cour_credit from\n" +
            "(\n" +
            "select user.job_number,user.ddid from user \n" +
            "where  \n" +
            "\t\t(case when user.name like #{studentName}  then (case when  #{studentName}  is not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{studentName}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            "\t\t and\n" +
            "\t\t (case when user.job_number=#{jobNumber}  then (case when  #{jobNumber}  is not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{jobNumber}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            ") as A left join \n" +
            "(\n" +
            "select concat(student_course.school_year,'-',student_course.term) as school_year_term,student_course.student_job_number,student_course.course_specific_code\n" +
            " from student_course \n" +
            "where  \n" +
            "\t\t(case when student_course.school_year=#{schoolYear}  then (case when  #{schoolYear}  is not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{schoolYear}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            "\t\t and\n" +
            "\t\t (case when student_course.term=#{term}  then (case when  #{term}  is not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{term}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            ")as B on A.job_number=B.student_job_number left join \n" +
            "(\n" +
            "\n" +
            "select course.cour_credit, course.cour_name as course_name, cour_attr as course_type,course.cour_code_specific from course\n" +
            "where  \n" +
            "\t\t(case when course.cour_name like #{courseName}  then (case when  #{courseName}  is not null then '1' else '0' end)\n" +
            "\t\t\t\n" +
            "\t\t\t else (case when  #{courseName}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            "\t\t and\n" +
            "\t\t (case when course.cour_attr = #{courseType}  then (case when #{courseType} is not null then '1' else '0' end)\n" +
            "\t\t\t\n" +
            "\t\t\t else (case when #{courseType} is not null then '0' else '1' end)\n" +
            "\t \t end ='1') \n" +
            ") as C on B.course_specific_code=C.cour_code_specific \n" +
            "left join student on A.ddid=student.user_id left join \n" +
            "(select major.name as major_name,major.college_name, major.`code` from major where  \n" +
            "case when major.college_name like #{collegeName}  then (case when  #{collegeName}\t\tis not null then '1' else '0' end)\n" +
            "\t\t\t else (case when  #{collegeName}  is not null then '0' else '1' end)\n" +
            "\t \t end ='1') as D on D.code=student.major_id")
    List<CourseTableExcelVo> getCourseTableVo2(
            @Param("collegeName") String collegeName,
            @Param("studentName") String studentName,
            @Param("jobNumber") String jobNumber,
            @Param("schoolYear") String schoolYear,
            @Param("term") String term,
            @Param("courseName") String courseName,
            @Param("courseType") String courseType);

    @Delete("DELETE \n" +
            "FROM \n" +
            "\tstudent_course \n" +
            "WHERE \n" +
            "\tCONCAT( \n" +
            "\t\tcourse_specific_code, \n" +
            "\tstudent_job_number, \n" +
            "\tschool_year,term) IN ( SELECT A.aa from ( \n" +
            "SELECT \n" +
            "\tCONCAT( \n" +
            "\t\tcourse_specific_code, \n" +
            "\tstudent_job_number, \n" +
            "\tschool_year,term) as aa \n" +
            "FROM \n" +
            "\tstudent_course \n" +
            "GROUP BY \n" +
            "\taa  \n" +
            "HAVING \n" +
            "\tcount( aa ) > 1) as  A ) \n" +
            "\tAND id NOT IN ( SELECT B.mid from ( \n" +
            "SELECT \n" +
            "\tmin( id ) as mid \n" +
            "\t \n" +
            "FROM \n" +
            "\tstudent_course  \n" +
            "GROUP BY \n" +
            "\tCONCAT( \n" +
            "\t\tcourse_specific_code, \n" +
            "\tstudent_job_number, \n" +
            "\tschool_year,term)  \n" +
            "HAVING \n" +
            "\tcount( CONCAT( \n" +
            "\t\tcourse_specific_code, \n" +
            "\tstudent_job_number, \n" +
            "\tschool_year,term) ) >1) as B  ) ")
    void cleanMultipartData();
}
